package com.zqxq.lottery.common.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.util.PoiPublicUtil;
import cn.hutool.poi.excel.WorkbookUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

public abstract class ExcelUtils <T>{
    /**
     * 导出为Excel
     * @param response  输出流对象
     * @param listData 数据
     * @param excelTitle    excel标题
     * @param excelSheetName    excel工作空间名称
     * @see <{@link  }>
     * @return  boolean true=成功,false=失败
     */
    public static boolean downloadExcel(HttpServletResponse response, List listData,Class dataBean, String excelTitle, String excelSheetName) {

        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(excelTitle, excelSheetName),
                dataBean, listData);
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            workbook.write(os);
            byte[] bytes = os.toByteArray();
            String fileName = UUID.randomUUID() + ".xls";
            response.reset();
            response.setContentType("application/msexcel;charset=utf-8");
            response.setHeader("Content-disposition", "attachment;filename= " + fileName);
            response.getOutputStream().write(bytes);
            response.getOutputStream().flush();
            response.getOutputStream().close();

        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
    * @Author 苦智
    * @Description 导入excel到数据库  excel表格列名与数据库实体属性一一对应（@Excel(name属性值)与列名一样且顺序一一对应）
    * @Date 16:15 2018/9/20
    * @Param multipartFile excel文件
     * @Param obj 数据库对象
    * @return java.util.List<java.lang.Object>
    **/
    public static List<Object> importExcel(MultipartFile multipartFile,Object obj) {
        Class<?> pojoClass = obj.getClass();
        List<Object> list = new ArrayList();
//        Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
        //获取所有的字段(包括私有、受保护、默认的)
        Field[] fields = pojoClass.getDeclaredFields();
        String fileName = multipartFile.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$"))
            return null;
        Sheet sheet = getSheet(multipartFile);
        //行数
        int rowNum = sheet.getLastRowNum();
        if (rowNum <= 1)
            return null;
        Row row1 = sheet.getRow(0);
        //列数
        short cellNum = row1.getLastCellNum();
        if (cellNum != fields.length)
            return null;
        for (int r = 1;r<=rowNum;r++){
            try {
                obj = obj.getClass().newInstance();
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            for (int n = 0;n<cellNum;n++) {
                Excel excel = (Excel)fields[n].getAnnotation(Excel.class);
                //列名(注解上的name属性值)
                String targetId = getTargetId(pojoClass);
                String name = PoiPublicUtil.getValueByTargetId(excel.name(), targetId, (String)null);
                String fieldName = fields[n].getName();
                Class<?> type = fields[n].getType();
                String cellValue = row1.getCell(n).getStringCellValue();
                if (name.equals(cellValue)){
                    Row row = sheet.getRow(r);
                    Method setReadOnly = null;
                    try {
                        setReadOnly = pojoClass.getDeclaredMethod(parSetName(fieldName), type);
                        String value = getCellValue(row.getCell(n));
                        setReadOnly.invoke(obj,value);
                    } catch (NoSuchMethodException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                    }
                }
            }
            list.add(obj);
        }
        return list;
    }

    private static Sheet getSheet(MultipartFile multipartFile){
        InputStream inputStream = null;
        try {
            inputStream = multipartFile.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        Workbook book = WorkbookUtil.createBook(inputStream, true);
        return book.getSheetAt(0);
    }

    private static String getTargetId(Class<?> pojoClass) {
        ExcelTarget etarget = (ExcelTarget)pojoClass.getAnnotation(ExcelTarget.class);
        return etarget == null ? null : etarget.value();
    }

    private static String parSetName(String fieldName) {
        if (null == fieldName || "".equals(fieldName)) {
            return null;
        }
        return "set" + fieldName.substring(0, 1).toUpperCase()
                + fieldName.substring(1);
    }

    public static String getCellValue(Cell cell) {
        String cellValue = "";
        DecimalFormat df = new DecimalFormat("#");
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                cellValue = cell.getRichStringCellValue().getString().trim();
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                cellValue = df.format(cell.getNumericCellValue()).toString();
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue = "";
        }
        return cellValue;
    }


}
